package com.yunhuakeji.wddx.utils;

import java.sql.*;
import java.util.List;
import com.yunhuakeji.wddx.utils.OracleConnection;
import com.yunhuakeji.wddx.utils.DataColumn;
import com.yunhuakeji.wddx.utils.OracleTransationColumnsType;

/**
 * 
 * @author FuWaer
 * @version 1.0
 * 
 */
public class OracleHelper {

	/**
	 * 
	 * @param connectionString
	 * @param commandType
	 * @param commandText
	 * @return
	 * @throws Exception
	 */
	public static DataSet ExecuteDataSet(String connectionString,
			CommandType commandType, String commandText) throws Exception {
		return ExecuteDataSet(new OracleConnection(connectionString),
				commandType, commandText);
	}

	/**
	 * 
	 * @param connectionString
	 * @param commandType
	 * @param commandText
	 * @param parameters
	 * @return
	 * @throws Exception
	 */
	public static DataSet ExecuteDataSet(String connectionString,
			CommandType commandType, String commandText,
			Parameter... parameters) throws Exception {
		return ExecuteDataSet(new OracleConnection(connectionString),
				commandType, commandText, parameters);
	}
	
	public static DataSet ExecuteDataSet(String connectionString,
			CommandType commandType, String commandText,
			List<Parameter> parameters) throws Exception {
		return ExecuteDataSet(new OracleConnection(connectionString),
				commandType, commandText, (Parameter[])parameters.toArray());
	}

	/**
	 * 
	 * @param connection
	 * @param commandType
	 * @param commandText
	 * @return
	 * @throws Exception
	 */
	public static DataSet ExecuteDataSet(OracleConnection connection,
			CommandType commandType, String commandText) throws Exception {
		DataSet ds =new DataSet();
		if(commandType.equals(CommandType.Text)){
			try {
				PreparedStatement p = connection.GetConnection().prepareStatement(commandText);
				ResultSet rs=p.executeQuery();
				ds.AddTable(ConvertResultSetToDataTable(rs));
			} catch (Exception e) {
				System.out.println(e.getMessage());
			}
		}
		return ds;
	}

	/**
	 * 
	 * @param connection
	 * @param commandType
	 * @param commandText
	 * @param parameters
	 * @return
	 * @throws Exception
	 */
	public static DataSet ExecuteDataSet(OracleConnection connection,
			CommandType commandType, String commandText,
			Parameter... parameters) throws Exception {

		DataSet ds = new DataSet();
		if (commandType.equals(CommandType.Text)) {	
			try {
				if(parameters.length>0)
				{
					PreparedStatement p = connection.GetConnection().prepareStatement(commandText);
					for (int i = 0; i < parameters.length; i++)
					{
						if(parameters[i].Value==null||parameters[i].Value.equals("null"))
						{
							p.setString(i + 1, "");
						}
						else p.setString(i + 1, parameters[i].Value.toString());
					}
					ResultSet rs = p.executeQuery();
					ds.AddTable(ConvertResultSetToDataTable(rs));
				}
				else
				{
					Statement s = connection.GetConnection().createStatement();
					ResultSet rs = s.executeQuery(commandText);
					ds.AddTable(ConvertResultSetToDataTable(rs));
				}
			} catch (Exception e) {
				System.out.println(e.getMessage());
			}
			finally {
				connection.Close();
			}
			
		} else if (commandType.equals(CommandType.StoreProcedure)) {

			Parameter[] paras = parameters;

			String sql = "";
			for (int i = 0; i < paras.length; i++) {
				sql = sql + "?,";
			}
			if (sql.length() > 0) {
				sql = "(" + sql.substring(0, sql.length() - 1) + ")";
			}

			sql = "{ call " + commandText + sql + " }";
			CallableStatement proc = null;
			proc = connection.GetConnection().prepareCall(sql);
			for (int i = 0; i < paras.length; i++) {
				Parameter p = paras[i];
				if (p.parameterDirection == ParameterDirection.IN) {
					proc.setObject(i + 1, p.Value, p.parameterType);
				} else if (p.parameterDirection == ParameterDirection.OUT) {
					proc.registerOutParameter(i + 1, p.parameterType);
				}
			}
			try {
				proc.execute();

				for (int i = 0; i < paras.length; i++) {
					Parameter p = paras[i];
					if (p.parameterDirection == ParameterDirection.OUT) {
						p.Value = proc.getObject(i + 1);
						if (p.parameterType == oracle.jdbc.OracleTypes.CURSOR) {
							ResultSet rs = (ResultSet) p.Value;
							DataTable dt = ConvertResultSetToDataTable(rs);
							DataSet _lds = new DataSet();
							_lds.AddTable(dt);
							p.Value = _lds;
							ds.AddTable(dt);
						}
					}
				}
			} catch (Exception e) {
				throw e;
			} finally {
				connection.Close();
			}

		} else {
			throw new Exception("commandType is invalid");
		}

		return ds;
	}

	/**
	 * 
	 * @param transaction
	 * @param commandType
	 * @param commandText
	 * @return
	 * @throws Exception
	 */
	public static DataSet ExecuteDataSet(OracleTransaction transaction,
			CommandType commandType, String commandText) throws Exception {
		return ExecuteDataSet(transaction.Connection, commandType, commandText);
	}

	/**
	 * 
	 * @param transaction
	 * @param commandType
	 * @param commandText
	 * @param parameters
	 * @return
	 * @throws Exception
	 */
	public static DataSet ExecuteDataSet(OracleTransaction transaction,
			CommandType commandType, String commandText,
			Parameter... parameters) throws Exception {
		return ExecuteDataSet(transaction.Connection, commandType, commandText,
				parameters);
	}

	/**
	 * 
	 * @param connectionString
	 * @param commandType
	 * @param commandText
	 * @throws Exception
	 */
	public static void ExecuteNonQuery(String connectionString,
			CommandType commandType, String commandText) throws Exception {
		ExecuteNonQuery(new OracleConnection(connectionString), commandType,
				commandText);
	}

	/**
	 * 
	 * @param connectionString
	 * @param commandType
	 * @param commandText
	 * @param parameters
	 * @throws Exception
	 */
	public static void ExecuteNonQuery(String connectionString,
			CommandType commandType, String commandText,
			Parameter... parameters) throws Exception {
		ExecuteNonQuery(new OracleConnection(connectionString), commandType,
				commandText, parameters);
	}

	/**
	 * 
	 * @param connection
	 * @param commandType
	 * @param commandText
	 * @throws Exception
	 */
	public static void ExecuteNonQuery(OracleConnection connection,
			CommandType commandType, String commandText) throws Exception {
		ExecuteNonQuery(connection, commandType, commandText, new Parameter[0]);
	}

	/**
	 * 
	 * @param connection
	 * @param commandType
	 * @param commandText
	 * @param parameters
	 * @throws Exception
	 */
	public static void ExecuteNonQuery(OracleConnection connection,
			CommandType commandType, String commandText,
			Parameter... parameters) throws Exception {
		
		if (commandType.equals(CommandType.Text)) {
			try {
				if(parameters.length>0)
				{
					PreparedStatement p = connection.GetConnection().prepareStatement(commandText);
					for (int i = 0; i < parameters.length; i++)
					{
						if(parameters[i].Value==null||parameters[i].Value.equals("null"))
						{
							p.setString(i + 1, "");
						}
						else p.setString(i + 1, parameters[i].Value.toString());
					}
					p.executeQuery();
				}
				else
				{
					Statement s = connection.GetConnection().createStatement();
					s.executeQuery(commandText);
				}
			} catch (Exception e) {
				System.out.println(e.getMessage());
			}
			finally{
				connection.Close();
			}
			
		} 
		 else if (commandType.equals(CommandType.StoreProcedure)) {

			Parameter[] paras = parameters;

			String sql = "";
			for (int i = 0; i < paras.length; i++) {
				sql = sql + "?,";
			}
			if (sql.length() > 0) {
				sql = "(" + sql.substring(0, sql.length() - 1) + ")";
			}

			sql = "{ call " + commandText + sql + " }";
			CallableStatement proc = null;
			proc = connection.GetConnection().prepareCall(sql);
			for (int i = 0; i < paras.length; i++) {
				Parameter p = paras[i];
				if (p.parameterDirection == ParameterDirection.IN) {
					proc.setObject(i + 1, p.Value, p.parameterType);
				} else if (p.parameterDirection == ParameterDirection.OUT) {
					proc.registerOutParameter(i + 1, p.parameterType);
				}
			}
			try {
				proc.execute();

				for (int i = 0; i < paras.length; i++) {
					Parameter p = paras[i];
					if (p.parameterDirection == ParameterDirection.OUT) {
						p.Value = proc.getObject(i + 1);
						if (p.parameterType == oracle.jdbc.OracleTypes.CURSOR) {
							ResultSet rs = (ResultSet) p.Value;
							DataTable dt = ConvertResultSetToDataTable(rs);
							DataSet _lds = new DataSet();
							_lds.AddTable(dt);
							p.Value = _lds;
						}
					}
				}
			} catch (Exception e) {
				throw e;
			} finally {
				connection.Close();
			}

		} else {
			throw new Exception("commandType is invalid");
		}	
	}

	/**
	 * 
	 * @param transaction
	 * @param commandType
	 * @param commandText
	 * @throws Exception
	 */
	public static void ExecuteNonQuery(OracleTransaction transaction,
			CommandType commandType, String commandText) throws Exception {
		ExecuteNonQuery(transaction.Connection, commandType, commandText);
	}

	/**
	 * 
	 * @param transaction
	 * @param commandType
	 * @param commandText
	 * @param parameters
	 * @throws Exception
	 */
	public static void ExecuteNonQuery(OracleTransaction transaction,
			CommandType commandType, String commandText,
			Parameter... parameters) throws Exception {
		ExecuteNonQuery(transaction.Connection, commandType, commandText,
				parameters);
	}

	/**
	 * ��ResultSetװ��ΪDataTable�ĺ���
	 * 
	 * @param rs
	 * @return
	 * @throws Exception
	 */
	private static DataTable ConvertResultSetToDataTable(ResultSet rs)
			throws Exception {
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();

		DataTable dt = new DataTable();
		while (rs.next()) {
			DataRow dr = new DataRow();
			for (int i = 1; i <= columnCount; i++) {
				DataColumn dc=null;
				if(rsmd.getColumnType(i)==2005){
					//�ж�CLOB����ǿת
					try {
						if(rs.getObject(i)!=null)
						{
							dc= new DataColumn(rsmd.getColumnName(i), OracleTransationColumnsType.ClobToString((Clob)rs.getObject(i)));
						}
						else dc=new DataColumn(rsmd.getColumnName(i),"-");
					} catch (Exception e) {
						System.out.println("error:"+e.getMessage());
					}
				}
				else{
					dc = new DataColumn(rsmd.getColumnName(i), rs.getObject(i));
				}
				dr.AddColumn(dc);
			}
			dt.AddRow(dr);
		}
		return dt;
	}
	
	
	public static int  SelectToResoultCount(OracleConnection connection,
			CommandType commandType, String commandText,
			Parameter... parameters) throws Exception {
		int rowcount=0;
		if (commandType.equals(CommandType.Text)) {
			if(parameters.length>0)
			{
				PreparedStatement p = connection.GetConnection().prepareStatement(commandText);
				for (int i = 0; i < parameters.length; i++) {
					p.setString(i+1, parameters[i].Value.toString());
				}
				ResultSet rs =p.executeQuery();
				try {
					while(rs.next())
					{
						rowcount++;
					}
				} catch (Exception e) {
					rowcount=0;
				}
			}
			else
			{
				Statement s = connection.GetConnection().createStatement();
				ResultSet rs=s.executeQuery(commandText);
				try {
					while(rs.next())
					{
						rowcount++;
					}
				} catch (Exception e) {
					rowcount=0;
				}
			}
		} else if (commandType.equals(CommandType.StoreProcedure)) {

		} else {
			throw new Exception("commandType is invalid");
		}
		return rowcount;
	}
	
}
